In this article, we will learn how to search and filter the GridView by using a TextBox. The OnTextChanged
event handler will fire when TextBox gets changed. To use the Text changed event you need to add the related event handler in your code and set the AutoPostBack
to true through HTML.
How to Search and Filter GridView in ASP.NET?
Whenever the OnTextChanged
event is being triggered, the database records will be searched and the records after the filtering process will be displayed in Asp.Net GridView.
The Database
Create a database called ‘customersdb’ and one simple table customers with the following schema.
CustomerName (nvarchar(50),null)
CustomerEmail (nvarchar(50),null)
CustomerCity (nvarchar(50),null)
Or simply run the SQL below to create that table:
BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Customers ( CustomerName nvarchar(50) NULL, CustomerEmail nvarchar(50) NULL, CustomerCity nvarchar(50) NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Customers SET (LOCK_ESCALATION = TABLE) GO COMMIT
HTML markup
<div> <h1>Customer List</h1> <asp:Label runat="server">Search by Name</asp:Label><asp:TextBox ID="txtSearch" runat="server" OnTextChanged="SearchEvent" AutoPostBack="true"></asp:TextBox> <hr /> <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPaging"> <Columns> <asp:BoundField DataField="CustomerName" HeaderText="Name" ItemStyle-Width="150" /> <asp:BoundField DataField="CustomerEmail" HeaderText="Email" ItemStyle-Width="150" /> <asp:BoundField DataField="CustomerCity" HeaderText="City" ItemStyle-Width="150" /> </Columns> </asp:GridView> </div>
You need to import the following namespaces.
using System.Data; using System.Data.SqlClient;
The SearchCustomers()
method is called from the page load event, as well as when the textbox value has been changed. I will pass the value of the textbox and add it as a parameter to the SQLCommand
.
Page_Load Event
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { SearchCustomers(); } }
SearchEvent Function
This function is triggered whenever the TextBox value is changed (OnTextChanged
)
protected void SearchEvent(object sender, EventArgs e) { SearchCustomers(); }
SearchCustomer Function
This is the main function that binds the DataGrid based on the value of the TextBox control.
private void SearchCustomers() { string conn_str = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True"; using (SqlConnection con = new SqlConnection(conn_str)) { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = "SELECT * FROM Customers Where CustomerName Like @CustomerName + '%'"; cmd.Parameters.AddWithValue("@CustomerName", txtSearch.Text.Trim()); cmd.Connection = con; using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); gvCustomers.DataSource = dt; gvCustomers.DataBind(); } } } }
Add Pagination to the GridView
The OnPaging
event handler handles the OnPageIndexChanging
event. The GridView’s PageIndex property is set and the SearchCustomers()
method is called.
protected void OnPaging(object sender, GridViewPageEventArgs e) { gvCustomers.PageIndex = e.NewPageIndex; SearchCustomers(); }
Screenshot
Comments